﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace fanli.DAL
{
    /// <summary>
    /// 数据访问类fanli_shop。
    /// </summary>
    public class fanli_shop
    {
        iData.DataAcess worker = new iData.DataAcess();
        public fanli_shop()
        { }

        #region  基本方法（系统生成）

        #region GetMaxId
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            string sql = "select max(id) from fanli_shop";
            return worker.ExecuteCount(sql);
        }
        /// <summary>
        /// 得到最大ID (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public int GetMaxId(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select max(id) from fanli_shop";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            return worker.ExecuteCount(sql);
        }

        #endregion

        #region Exists
        /// <summary>
        /// 是否存在该记录 (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public bool Exists(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select count(*) from fanli_shop";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            int num = worker.ExecuteCount(sql, paraList);
            return num == 0 ? false : true;
        } 
        #endregion

        #region Add
        /// <summary>
        /// 增加一条数据,返回自增的id
        /// </summary>
        public int Add(fanli.Model.fanli_shop model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into fanli_shop(");
            strSql.Append("title,logoUrl,cataid,fanli,vipFanli,summary,about,notice,bbsUrl,pubDate,sortid,lm,gotoURl,status,ext1,ext2,ext3,ext4)");
            strSql.Append(" values (");
            strSql.Append(" @title, @logoUrl, @cataid, @fanli, @vipFanli, @summary, @about, @notice, @bbsUrl, @pubDate, @sortid, @lm, @gotoURl, @status, @ext1, @ext2, @ext3, @ext4)");
            //strSql.Append(";select @@IDENTITY");
            List<SqlParameter> list = new List<SqlParameter>();
			list.Add(new SqlParameter("@title",model.title ));
			list.Add(new SqlParameter("@logoUrl",model.logoUrl ));
			list.Add(new SqlParameter("@cataid",model.cataid ));
			list.Add(new SqlParameter("@fanli",model.fanli ));
			list.Add(new SqlParameter("@vipFanli",model.vipFanli ));
			list.Add(new SqlParameter("@summary",model.summary ));
			list.Add(new SqlParameter("@about",model.about ));
			list.Add(new SqlParameter("@notice",model.notice ));
			list.Add(new SqlParameter("@bbsUrl",model.bbsUrl ));
			list.Add(new SqlParameter("@pubDate",model.pubDate ));
			list.Add(new SqlParameter("@sortid",model.sortid ));
			list.Add(new SqlParameter("@lm",model.lm ));
			list.Add(new SqlParameter("@gotoURl",model.gotoURl ));
			list.Add(new SqlParameter("@status",model.status ));
			list.Add(new SqlParameter("@ext1",model.ext1 ));
			list.Add(new SqlParameter("@ext2",model.ext2 ));
			list.Add(new SqlParameter("@ext3",model.ext3 ));
			list.Add(new SqlParameter("@ext4",model.ext4 ));
            return worker.ExecuteInsert(strSql.ToString(), list);
        } 
        #endregion

        #region Update
        /// <summary>
        /// 更新一条数据,返回影响的行数（建议重载后使用）
        /// </summary>
        public int Update(fanli.Model.fanli_shop model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fanli_shop set ");
            strSql.Append("title=@title,");
            strSql.Append("logoUrl=@logoUrl,");
            strSql.Append("cataid=@cataid,");
            strSql.Append("fanli=@fanli,");
            strSql.Append("vipFanli=@vipFanli,");
            strSql.Append("summary=@summary,");
            strSql.Append("about=@about,");
            strSql.Append("notice=@notice,");
            strSql.Append("bbsUrl=@bbsUrl,");
            strSql.Append("pubDate=@pubDate,");
            strSql.Append("sortid=@sortid,");
            strSql.Append("lm=@lm,");
            strSql.Append("gotoURl=@gotoURl,");
            strSql.Append("status=@status,");
            strSql.Append("ext1=@ext1,");
            strSql.Append("ext2=@ext2,");
            strSql.Append("ext3=@ext3,");
            strSql.Append("ext4=@ext4");
            strSql.Append(" where id=@id ");

            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", model.id));//id
            list.Add(new SqlParameter("@title", model.title));//商城名称
            list.Add(new SqlParameter("@logoUrl", model.logoUrl));//logo地址
            list.Add(new SqlParameter("@cataid", model.cataid));//商城类别
            list.Add(new SqlParameter("@fanli", model.fanli));//返利程度
            list.Add(new SqlParameter("@vipFanli", model.vipFanli));//vip返利
            list.Add(new SqlParameter("@summary", model.summary));//摘要
            list.Add(new SqlParameter("@about", model.about));//详细介绍
            list.Add(new SqlParameter("@notice", model.notice));//注意事项
            list.Add(new SqlParameter("@bbsUrl", model.bbsUrl));//论坛链接
            list.Add(new SqlParameter("@pubDate", model.pubDate));//发布时间
            list.Add(new SqlParameter("@sortid", model.sortid));//排序
            list.Add(new SqlParameter("@lm", model.lm));//所属联盟
            list.Add(new SqlParameter("@gotoURl", model.gotoURl));//推广地址
            list.Add(new SqlParameter("@status", model.status));//商城状态
            list.Add(new SqlParameter("@ext1", model.ext1));//扩展1
            list.Add(new SqlParameter("@ext2", model.ext2));//扩展2
            list.Add(new SqlParameter("@ext3", model.ext3));//扩展3
            list.Add(new SqlParameter("@ext4", model.ext4));//扩展4

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 执行sql语句并返回影响记录条数（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public int Update(string strUpdateSql, List<SqlParameter> cmdParms)
        {
            strUpdateSql = strUpdateSql.Replace("[tbl]", "fanli_shop");
            return worker.ExecuteNonQuery(strUpdateSql, cmdParms);
        }

        #endregion

        #region Delete
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fanli_shop ");
            strSql.Append(" where id=@id ");
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", id));

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 根据where语句删除数据
        /// </summary>
        public int Delete(string strWhere, List<SqlParameter> paraList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fanli_shop ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            return worker.ExecuteNonQuery(strSql.ToString(), paraList);
        } 
        #endregion

        #region  获得一个字段值
        /// <summary>
        /// 获得数据列表(参数化)
        /// </summary>
        public string GetField(string fieldName, string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 ");
            strSql.Append(fieldName);
            strSql.Append(" FROM fanli_shop ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteScalar(strSql.ToString(), cmdParms);
        }


        /// <summary>
        /// 获得记录条数count(*)
        /// </summary>
        public int GetCount(string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) ");
            strSql.Append(" FROM fanli_shop ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteCount(strSql.ToString(), cmdParms);
        }
        #endregion

        #region  获得一条记录

        /// <summary>
        /// 用sql语句返回一个datarow（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public DataRow GetRowBySql(string strSelectSql, List<SqlParameter> cmdParams)
        {
            return worker.ExecuteOneRow(strSelectSql, cmdParams);
        }

        /// <summary>
        /// 获得一条记录
        /// </summary>
        public DataRow GetRow(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == null|| dataFields=="")  dataFields = "id,title,logoUrl,cataid,fanli,vipFanli,summary,about,notice,bbsUrl,pubDate,sortid,lm,gotoURl,status,ext1,ext2,ext3,ext4";
            strSql.Append("select top 1 ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_shop ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteOneRow(strSql.ToString(), cmdParms);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.fanli_shop GetModel(string strWhere, List<SqlParameter> cmdParms)
        {
            DataRow dr = GetRow(strWhere, cmdParms ,"");
            return dr==null?null:DatarowToModel(dr);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.fanli_shop GetModel(int id)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@id", id));
            DataRow dr = GetRow("id=@id", paraList ,"");
            return dr==null?null:DatarowToModel(dr);
        }
        #endregion

        #region  获得dataset列表
        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        public DataSet GetDatasetBySql(string strSelectSql)
        {
            return worker.Execute(strSelectSql);
        }

        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        public DataSet GetDatasetBySql(string strSelectSql, List<SqlParameter> cmdParms)
        {
            return worker.Execute(strSelectSql, cmdParms);
        }


        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        /// <param name="tblName">表名，支持多表</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields, string tblName)
        {
            return worker.ExecutePageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, tblName);
        }
        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields)
        {
            return GetDatasetByPageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, "fanli_shop");
        }

        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public DataSet GetDataset(string strWhere)
        {
            return GetDataset(strWhere, null, "");
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,title,logoUrl,cataid,fanli,vipFanli,summary,about,notice,bbsUrl,pubDate,sortid,lm,gotoURl,status,ext1,ext2,ext3,ext4";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_shop ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.Execute(strSql.ToString(), cmdParms);
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields,int topN)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,title,logoUrl,cataid,fanli,vipFanli,summary,about,notice,bbsUrl,pubDate,sortid,lm,gotoURl,status,ext1,ext2,ext3,ext4";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_shop ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteTop(strSql.ToString(), cmdParms, topN);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public List<Model.fanli_shop> GetModelList(string strWhere)
        {
            DataSet dset = GetDataset(strWhere, null, "");
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public List<Model.fanli_shop> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields);
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public List<Model.fanli_shop> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields, int topN)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields, topN);
            if (dset == null) return null;
            else return DatatableToModelList(dset.Tables[0]);
        }
        #endregion

        #region  change to model
        /// <summary>
        /// 把数据从datatable转存成modellist
        /// </summary>
        public List<Model.fanli_shop> DatatableToModelList(DataTable dt)
        {
            List<Model.fanli_shop> modelList = new List<Model.fanli_shop>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                for (int n = 0; n < rowsCount; n++)
                {
                    Model.fanli_shop model = new Model.fanli_shop();
                    model = DatarowToModel(dt.Rows[n]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// 把数据从datarow转存成model
        /// </summary>
        public fanli.Model.fanli_shop DatarowToModel(DataRow dr)
        {
            Model.fanli_shop model = new Model.fanli_shop();
            model.id = dr["id"] == DBNull.Value ? 0 : int.Parse(dr["id"].ToString());//id
            model.title = dr["title"].ToString();//商城名称
            model.logoUrl = dr["logoUrl"].ToString();//logo地址
            model.cataid = dr["cataid"] == DBNull.Value ? 0 : int.Parse(dr["cataid"].ToString());//商城类别
            model.fanli = dr["fanli"].ToString();//返利程度
            model.vipFanli = dr["vipFanli"].ToString();//vip返利
            model.summary = dr["summary"].ToString();//摘要
            model.about = dr["about"].ToString();//详细介绍
            model.notice = dr["notice"].ToString();//注意事项
            model.bbsUrl = dr["bbsUrl"].ToString();//论坛链接
            model.pubDate = dr["pubDate"] == DBNull.Value ? DateTime.Parse("2000-1-1") : DateTime.Parse(dr["pubDate"].ToString());//发布时间
            model.sortid = dr["sortid"] == DBNull.Value ? 0 : int.Parse(dr["sortid"].ToString());//排序
            model.lm = dr["lm"].ToString();//所属联盟
            model.gotoURl = dr["gotoURl"].ToString();//推广地址
            model.status = dr["status"] == DBNull.Value ? 0 : int.Parse(dr["status"].ToString());//商城状态
            model.ext1 = dr["ext1"].ToString();//扩展1
            model.ext2 = dr["ext2"].ToString();//扩展2
            model.ext3 = dr["ext3"].ToString();//扩展3
            model.ext4 = dr["ext4"].ToString();//扩展4
            return model;
        }


        #endregion

        #endregion

        #region 自定义方法（非系统生成的方法，请在此区域单独写函数，以便维护）

        /// <summary>
        /// 函数功能：示例函数
        /// 作者：buzhizhe 创建时间：2011.6.22
        /// 修改者：buzhizhe 最后修改时间：2011.6.22
        /// </summary>
        public void YourFunction()
        {
            ///
        } 


        #endregion
    }
}



